<?php
// +----------------------------------------------------------------------
// | OneThink [ WE CAN DO IT JUST THINK IT ]
// +----------------------------------------------------------------------
// | Copyright (c) 2013 http://www.onethink.cn All rights reserved.
// +----------------------------------------------------------------------
// | Author: 麦当苗儿 <zuojiazi@vip.qq.com> <http://www.zjzit.cn>
// +----------------------------------------------------------------------

namespace Admin\Controller;
use Think\Model;
class SubproController extends AdminController {
    public function index(){
        //查状态
        $sql="select count(CASE WHEN a.cnt<=b.cnt_low THEN 1 END) as low_cnt,
        count(CASE WHEN b.cnt_low<a.cnt and a.cnt<=b.cnt_poor THEN 2 END) as poor_cnt,
        count(CASE WHEN a.cnt>b.cnt_poor THEN 3 END) as normal_cnt
        from gr_vsubpro_inventory a,gr_subpro_product b where cnt>0 and a.BIN=b.BIN";
        $model=new Model();
        $res=$model->query($sql);
        //茶盘店
        $sql2="select checkAt from gr_subpro_batch  order by checkAt desc limit 1";
        $res2=$model->query($sql2);
        //查总价
        $sql3="select SUM(totalprice) as total_price from gr_vsubpro_inventory ";
        $res3=$model->query($sql3);
        //查消耗记录
        $time=time();
        $yesdaystart=strtotime(date('Y-m-d',time()))-86400;
        $yesdayend=$yesdaystart+86400;
        $sevendaystart=$yesdaystart-7*86400;
//        $sevendayend=$sevendaystart+86400;
        $halfmonthstart=$yesdaystart-15*86400;
//        $halfmonthend=$halfmonthstart+86400;
        $monthstart=$yesdaystart-30*86400;
//        $monthend=$monthstart+86400;

        $sql4="select (select sum(a.totalprice) from gr_subpro_act a where a.createAt>$yesdaystart and a.createAt<$yesdayend and a.type='out') as yescnt,
                (select sum(a.totalprice) from gr_subpro_act a where a.createAt>$sevendaystart and a.createAt<$yesdayend and a.type='out')/7 as sevencnt,
                (select sum(a.totalprice) from gr_subpro_act a where a.createAt>$halfmonthstart and a.createAt<$yesdayend and a.type='out')/15 as halfmonthcnt,
                (select sum(a.totalprice) from gr_subpro_act a where a.createAt>$monthstart and a.createAt<$yesdayend and a.type='out')/30 as monthcnt
                from gr_subpro_act a limit 1 ";
        $res4=$model->query($sql4);
//        dump($res4);
        $this->cntstatus=$res[0];
        $this->pd=$res2[0];
        $this->total_price=$res3[0];
        $this->xiaohao=$res4[0];
        $this->display();
    }
    public function detail($BIN=""){
        $map['BIN']=$BIN;
        $list = $this->lists('subpro_act', $map,'updateAt desc');
        int_to_string($list);
        $this->assign('_list', $list);
        $this->assign('_uid', UID);
        $this->meta_title = '数据列表';
        $this->display();

    }
    /**
     * 消耗品入库
     * @author Scor
     * 2016-02-25
     */
    public function inlist() {
        $Model = new Model();
        $sql = "select *,date_format(from_unixtime(createAt),'%Y%m%d') as day from gr_subpro_act where type='in' GROUP by date_format(from_unixtime(createAt),'%Y%m%d') order by createAt desc ";
        $list1 = $Model->query($sql);
        $total = count($list1);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }
        $page = new \Think\Page($total, $listRows, $REQUEST);
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        $sql.= " limit ".$page->firstRow . ',' . $page->listRows;
        $list = $Model->query($sql);
        $this->assign('_list', $list);
        $this->display('inlist');
    }
    /**
     * 消耗品出库
     * @author 张海涛
     * 2016-02-26
     */
    public function outlist() {

        $Model = new Model();

        $sql = "select *,date_format(from_unixtime(createAt),'%Y%m%d') as day from gr_subpro_act where type='out' GROUP by date_format(from_unixtime(createAt),'%Y%m%d') order by createAt desc ";
        $list1 = $Model->query($sql);
        $total = count($list1);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }

        $page = new \Think\Page($total, $listRows, $REQUEST);
        
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        
        $sql.= "limit ".$page->firstRow . ',' . $page->listRows;
        $list = $Model->query($sql);
        
        $this->assign('_list', $list);
        $this->meta_title = '数据列表';
        $this->display('outlist');
    }
    /**
     * 入库商品列表
     * @author James
     * 2016-01-15
     */
    public function indetail($day="",$dpid="") {
        $start_time=strtotime($day);
        $end_time=strtotime($day)+86400;
        if($dpid){
            $map['dpid']=$dpid;
        }
        $map['createAt']=array('between',[$start_time,$end_time]);
        $map['status']  =   1;
        $map['type']  = 'in';
        $list = $this->lists('subpro_act', $map,'updateAt desc');
        int_to_string($list);
        $departList=M('Subpro_department')->where(array('status'=>1))->select();
        $this->departmentList=$departList;
        $this->assign('_list', $list);
        $this->assign('_uid', UID);
        $this->meta_title = '数据列表';
        $this->display();
    }
    /**
     * 出库商品列表
     * @author
     * 2016-01-15
     */
    public function outdetail($day="") {
        $start_time=strtotime($day);
        $end_time=strtotime($day)+86400;
        $map['createAt']=array('between',[$start_time,$end_time]);
        $map['status']  =   1;
        $map['type']  = 'out';
        $list = $this->lists('subpro_act', $map,'updateAt desc');
        int_to_string($list);
        $this->assign('_list', $list);
        $this->assign('_uid', UID);
        $this->meta_title = '数据列表';
        $this->display();
    }
    /**
     * 入库
     * @author James
     * 2016-01-14
     */
    public function inadd($BIN="",$pname="",$unit="",$number="",$spec="",$totalprice="",$mother_unit="",$son_unit="",$mother_to_son=""){
        if(IS_POST){
            if($unit==""||$BIN==""||$pname==""||$number==""||$unit==""||$spec==""||$totalprice==""){
                $this->error("入库信息不完整");
            }
            $batch=$BIN.date('Ymd',time());
            $Inventory = M("subpro_batch");
            //判断该商品是否存在实时库存表（gr_inventory）内
            $res = $Inventory->where(array('BIN'=>$BIN,'batch'=>$batch))->find();
            if($res){
                $oldtotalprice = $res['totalprice'];
                $oldcnt = $res['cnt'];
                $cnt = $oldcnt + $number;
                $newtotalprice = $oldtotalprice + $totalprice;
                $unitprice = $newtotalprice/$cnt;
                $Inventory->where($res)->save(array('cnt'=>$cnt,'unitprice'=>$unitprice,'totalprice'=>$newtotalprice));
            }else{
                //入库时，如果该商品不在实时库存表（gr_inventory）内，则往实时库存表（gr_inventory）内新增该商品
                $data['BIN'] = $BIN;
                $data['pname'] = $pname;
                $data['spec'] = $spec;
                $data['batch'] = $batch;
                $data['cnt'] = $number;
                $data['mother_unit'] = $mother_unit;
                $data['son_unit'] = $son_unit;
                $data['mother_to_son'] = $mother_to_son;
                $data['unit'] = $unit;
                $data['unitprice'] = $totalprice/$number;
                $data['totalprice'] = $totalprice;
                $data['status'] = 1;
                $data['createAt'] = NOW_TIME;
                $data['updateAt'] = NOW_TIME;
                $Inventory->add($data);
            }
            $vinventory = M('vsubpro_inventory')->where(array('BIN'=>$BIN))->find();
            $dataList = array(
                'batch' => $batch,
                'BIN' => $BIN,
                'spec' => $spec,
                'pname' =>$pname,
                'cnt' => $number,
                'unit' => $unit,
                'unitprice' => $totalprice/$number,
                'totalprice' => $totalprice,
                'status' => 1,
                'createAt' => NOW_TIME,
                'updateAt' => NOW_TIME,
                'uid'=>UID,
                'type'=>'in',
                'aftercnt'=> $vinventory['cnt'],
                'operate'=>"+",
                'toname'=>''
            );
            D('subpro_act')->add($dataList);
            $this->success('入库成功！',U('subpro/inlist'));
        }else{
            $this->meta_title = '消耗品入库管理';
            $this->display();
        }
    }
    /**
     * 出库
     * @author 张海涛
     * 2016-02-26
     */
    public function outadd($BIN="",$batch="",$toname="",$number=""){
        if(IS_POST){
            if($BIN==""||$batch==""||$toname==""||$number==""){
                $this->error("出库信息不完整");
            }
            $Inventory = M("subpro_batch");
            $map['BIN']=$BIN;
            $map['batch']=$batch;
            $info = $Inventory->where($map)->find();
            $totalprice = $number*$info['unitprice'];

            $Inventory->where($map)->setDec('cnt',$number);
            $Inventory->where($map)->setDec('totalprice',$totalprice);
            $vinventory = M('vsubpro_inventory')->where(array('BIN'=>$BIN))->find();
            $dataList = array(
                'batch' => $batch,
                'BIN' => $BIN,
                'spec' => $info['spec'],
                'pname' =>$info['pname'],
                'cnt' => $number,
                'unit' => $info['unit'],
                'unitprice' => $info['unitprice'],
                'totalprice' => $totalprice,
                'status' => 1,
                'createAt' => NOW_TIME,
                'updateAt' => NOW_TIME,
                'uid'=>UID,
                'type'=>'out',
                'toname'=>$toname,
                'aftercnt'=>$vinventory['cnt'],
                'operate'=>"-"
            );
            D('subpro_act')->add($dataList);
            $this->success("操作成功",U('subpro/outlist'));
        }else{
            $this->meta_title = '出库管理';
            $this->display();

        }
    }
    /**
     * 实时库存列表
     */
    public function inventorylist(){
        $map['status']=1;
        $title = I('title');
        if($title){
            $map['pname'] = array('like', '%' . (string) $title . '%');
        }
        $list = $this->lists('subpro_inventory', $map,"createdAt DESC");
        int_to_string($list);
        $this->assign('_list', $list);
        
        $classList = M("classify")->where(array('status'=>1))->select();
        $data['classList'] = $classList;
        $this->data = $data;
        if(UID ==6){
            $oper = 1;
        }else{
            $oper = 0;
        }
        
        $this->assign('_oper', $oper);
//        $this->assign('_oper', 1);

        $this->meta_title = '数据列表';
        $this->display();
    }
    public function subinventory($title="",$cntstatus=0){
        $model=new Model();
        $sql="select a.*,b.cnt_poor,b.cnt_low,CASE WHEN a.cnt<=b.cnt_low THEN 1 ".
        "WHEN b.cnt_low<a.cnt and a.cnt<=b.cnt_poor THEN 2 ".
        "WHEN a.cnt>b.cnt_poor THEN 3 END as cntstatus".
        " from gr_vsubpro_inventory a,gr_subpro_product b where cnt>0 and a.BIN=b.BIN";
        if($title!=""){
            $sql.=" and a.pname like '%".$title."%'";
        }
        switch ($cntstatus){
            case 0:
                break;
            case 1:
                $sql.=" and a.cnt<=b.cnt_low ";
                break;
            case 2:
                $sql.=" and a.cn>b.cnt_low and a.cnt<b.cnt_poor ";
                break;
            case 3:
                $sql.=" and a.cnt>b.cnt_poor ";
                break;
        }
        $res = $model->query($sql);
        $total = count($res);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }
        $page = new \Think\Page($total, $listRows, $REQUEST);
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        $sql.=" limit $page->firstRow ,$page->listRows";
        $res = $model->query($sql);
        $this->assign("_list",$res);
        $this->meta_title = '数据列表';
        $this->cntstatus = $cntstatus;
        $this->display();
    }
    /**
     * 盘点记录
     */
    public function checkinfolist(){
        $sql = "select a.*,date_format(from_unixtime(a.createAt),'%Y%m%d') as day from gr_subpro_act a where a.type='check' GROUP by date_format(from_unixtime(a.createAt),'%Y%m%d') order by a.createAt desc ";
        $model = new Model();
        $list=$model->query($sql);
        $this->assign('_list', $list);
        $this->meta_title = '数据列表';
        $this->display();
    }
    /**
     * 盘点详细列表
     */
    public function checkflowlist($day="",$dpid=""){
        $start=strtotime($day);
        $end=strtotime($day)+86400;
        if($dpid){
            $map['dpid']=$dpid;
        }
        $map['createAt']=array('between',[$start,$end]);
        $map['type']="check";
        $list = $this->lists('subpro_act', $map,'createAt desc');
        $this->assign('_list',$list);
        $departList=M('Subpro_department')->where(array('status'=>1))->select();
        $this->departmentList=$departList;
        $this->meta_title = '数据列表';
        $this->display();
    }
    /**
     * 盘点
     */
    public function checkflow($BIN="",$batch="",$aftercnt=""){
        if(IS_POST){
            $now = NOW_TIME;
            if ($BIN == ""  || $batch == ""||$aftercnt=="") {
                $this->error("盘点信息不能为空");
            }
            $map['BIN']=$BIN;
            $map['batch']=$batch;
            $info=M('subpro_batch')->where($map)->find();
            if($info['cnt']>$aftercnt){
                $number = $info['cnt']-$aftercnt;
                $operate="-";
            }else{
                $number = $aftercnt-$info['cnt'];
                $operate="+";
            }

            $data=array(
                'totalprice'=>$info['unitprice']*$aftercnt,
                'cnt'=>$aftercnt,
                'checktime'=>time()
            );

            $inventory = M("subpro_batch");
            $inventory-> where($map)->save($data);
            $vinventory = M('vsubpro_inventory')->where(array('BIN'=>$BIN))->find();
            $dataList= array(
                'batch' => $batch,
                'BIN' => $BIN,
                'spec' => $info['spec'],
                'pname' =>$info['pname'],
                'cnt' => $number,
                'unit' => $info['unit'],
                'unitprice' => $info['unitprice'],
                'totalprice' => $info['unitprice']*$aftercnt,
                'status' => 1,
                'createAt' => NOW_TIME,
                'updateAt' => NOW_TIME,
                'uid'=>UID,
                'type'=>'check',
                'toname'=>'',
                'aftercnt'=>$vinventory['cnt'],
                'operate'=>$operate
            );
            D('subpro_act')->add($dataList);
            $this->success("操作成功",U("Subpro/checkinfolist"));
        }else{
            $this->meta_title = '数据列表';
            $this->display();
        }
    }
    //仓库管理
    /**
     * 实时库存导出
     * @author James
     * 2016-02-18
     */
    public function kcExport() {
        if (!IS_POST){
            $this->redirect("Subpro/inventorylist");
        }
        $where = "";
        $title = I('title');
        if($title){
            $where .= " and pname like '%".$title."%' ";
        }
        
        $sql = "select a.pname,a.spu,a.spec,a.cnt,a.unitprice,a.totalprice ,"
                . "FROM_UNIXTIME(a.updateAt) updateAt,"
                . "CASE WHEN a.status = 1 THEN '正常' WHEN a.status = 0 THEN '禁用' WHEN a.status = -1 THEN '删除' END STATUS "
                . "from gr_subpro_inventory a where 1=1 "
                . "ORDER BY createdAt desc";
//        $sql = "select * from gr_subpro_inventory  where 1=1 ORDER BY createdAt desc";
        $Model = new Model();
        $data = $Model->query($sql);
        if ($data) {
//            $orderTable = M('order');
//            $data1 = array('isExport' => 1);
//
//            $args = array_map(function($src){
//                                return $src['orderid'];}, $data);
//            $map['orderid']  = array('in',$args);
//            $result = $orderTable->where($map)->setField($data1);
            
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";
            
            $headArr = array("商品名称", "商品编号", "规格", "库存数量", "库存单价", "库存总价","盘点时间", "状态");
            
            $title = "消耗品实时库存列表";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * 上月消耗导出
     *
     */
    public function monthexport(){
        $monthend=strtotime(date("Y-m-1 00:00:00",time()))-1;
        $monthstart=strtotime(date("Y-m-1 00:00:00",$monthend));
        $sql="select toname, format(sum(totalprice),2) from gr_subpro_act where type='out' and createAt>$monthstart and createAt<$monthend GROUP by toname";
        $Model = new Model();
        $data = $Model->query($sql);
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");
            $filename = "output_order_excel";
            $headArr = array("门店","总消耗￥");
            $title = "月记门店消耗";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * @param string $title
     */
    public function subkcexport($title=""){
        $sql = "select a.pname,a.BIN,a.spec,a.unit,a.cnt,FORMAT(a.cnt/a.mother_to_son,2),FORMAT(a.unitprice,2),FORMAT(a.totalprice,2) "
            . "from gr_vsubpro_inventory a where 1=1 group by a.BIN";
        if($title!=""){
            $sql .= " and pname like '%".$title."%' ";
        }
        $Model = new Model();
        $data = $Model->query($sql);
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");
            $filename = "output_order_excel";
            $headArr = array("商品名称","商品编号","规格","单位", "库存数量1(小)","库存数量2(大)", "平均单价", "库存总价");
            $title = "消耗品实时库存列表";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    public function getExcel($fileName, $headArr, $data, $title) {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            //die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }
        // H:i:s
        $date = date("Y_m_d_H_i_s", time());
        //$date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //第一列设置报表时间范围
        $key = ord("A");
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $title);


        //第二列设置报表列头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '2', $v);
            $key += 1;
        }

        $column = 3;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
                $objActSheet->getColumnDimension('a')->setWidth(20);
                $objActSheet->getColumnDimension('b')->setWidth(20);
                if (count($headArr) == 12){
                    $objActSheet->getColumnDimension('d')->setWidth(20);
                    $objActSheet->getColumnDimension('h')->setWidth(20);
                    $objActSheet->getColumnDimension('i')->setWidth(20);
                    $objActSheet->getColumnDimension('j')->setWidth(30);
                    $objActSheet->getColumnDimension('l')->setWidth(100);
                }else{
                    $objActSheet->getColumnDimension('g')->setWidth(20);
                    $objActSheet->getColumnDimension('h')->setWidth(20);
                    $objActSheet->getColumnDimension('i')->setWidth(30);
                    $objActSheet->getColumnDimension('j')->setWidth(30);
                    $objActSheet->getColumnDimension('k')->setWidth(100);
                }
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }
    /***
     * 消耗品管理
     * @author Scor
     */
    public function manage($name=""){
        $name = $name;
        if($name!=""){
            $map['pname'] = array('like', '%'.$name.'%');
        }
        $map['status'] = array('egt',0);
        $map['classname'] = array('like', '%消耗品%');
        $this->meta_title = '消耗品信息';
        $list = $this->lists('subpro_product', $map);
        int_to_string($list);
        $this->assign('_list',$list);
        $this->display();
    }
    /**
     * 消耗品库存预警
     */
    public function cntwarn($id="",$cnt_poor="",$cnt_low=""){
        if(IS_POST){
            $map['id']=$id;
            $data['cnt_poor']=$cnt_poor;
            $data['cnt_low']=$cnt_low;
            M('subpro_product')->where($map)->save($data);
            $this->success("设置预警成功");
        }else{
            $map['id']=$id;
            $pro=M('subpro_product')->where($map)->find();
            $this->pro=$pro;
            $this->id=$id;
            $this->display();
        }
    }
//    public function index(){
//        $name = I('name');
//        $map['status']  =   array('egt',0);
//        $map['name']    =   array('like', '%'.$name.'%');
//
//        $this->meta_title = '商品信息';
//
//        $list = $this->lists('product', $map);
//        int_to_string($list);
//
//        Cookie('__forward__',$_SERVER['REQUEST_URI']);
//        $this->assign('_list',$list);
//        $this->display();
//    }
    public function createProduct(){
        if ( empty($this->product) ) {
            $this->assign('product',array('name'=>null,'id'=>null,'pinyin'=>null));//排除notice信息
        }
        $classList = M("classify")->where(array('status'=>1))->select();
        $data['classList'] = $classList;
        $this->data = $data;
        $this->meta_title = '新增商品';
        $this->display('editproduct');
    }
    public function delsubpro($id=""){
        M('Subpro_product')->where(array('id'=>$id))->save(array('status'=>-1));
        $this->success('删除成功');
    }
    public function editproduct(){
        $product = M('product')->find( (int)$_GET['id'] );
        $this->assign('product',json_encode($product));
        $this->assign('id',$product["id"]);
        $classname = $product['classname'];
        $this->assign('_classname',$classname);

        $classList = M("classify")->where(array('status'=>1))->select();
        $data['classList'] = $classList;
        $this->data = $data;

        $this->meta_title = '编辑商品';
        $this->display();
    }
    public function changeStatus($method=null){
        if ( empty($_REQUEST['id']) ) {
            $this->error('请选择要操作的数据!');
        }
        switch ( strtolower($method) ){
            case 'forbid':
                $this->forbid('product');
                break;
            case 'resume':
                $this->resume('product');
                break;
            case 'delete':
                $this->delete('product');
                break;
            default:
                $this->error($method.'参数非法');
        }
    }
    public function newSubPro(){
        $product = M('subpro_product');
        $rules = array(
            array('BIN','require','请输入编号！'),
            array('spec','require','请输入规格！'),
        );
        if(!$product->validate($rules)->create()){
            $this->error($rules->getError());
        }else{
            $product->create();
            $product->createAt = time();
            $product->updateAt = time();
            $product->status = 1;
            if($product->add()){
                $this->success("新增成功！",U('Subpro/manage'));
            }else{
                $this->error("新增失败，编号存在！");
            }
        }

    }
    public function inventorydetail($spec = "", $unitprice = "", $totalprice = "", $cnt = "",$remark="") {
        $id = I('id');
        if (IS_POST) {

            if ($spec == "") {
                $this->error("规格不能为空");
            }
            if ($cnt == "") {
                $this->error("库存数量不能为空");
            }
            if ($unitprice == "") {
                $this->error("单价不能为空");
            }
            if ($totalprice == "") {
                $this->error("总价不能为空");
            }

            if ($remark == "") {
                $this->error("修改原因不能为空");
            }
            $spu = I('spu');
            $pname = I('pname');

            $data1 = M('subpro_inventory')->where(array('id' => $id))->find();

            $spec1 = $data1['spec'];
            $cnt1 = $data1['cnt'];
            $unitprice1 = $data1['unitprice'];
            $totalprice1 = $data1['totalprice'];
            $str = "修改前:商品编号：".$spu.";商品名称：".$pname.";规格：".$spec1.";库存数量：".$cnt1.";单价：".$unitprice1.";总价：".$totalprice1.";修改后：商品编号：".$spu.";商品名称：".$pname.";规格：".$spec.";库存数量：".$cnt.";单价：".$unitprice.";总价：".$totalprice.";修改原因：".$remark;
            $data = array('spec' => $spec, 'cnt' => $cnt,'unitprice' => $unitprice,'totalprice' => $totalprice, 'updateAt' => NOW_TIME,'updateUser'=>UID);
            if (!M('subpro_inventory')->where(array('id' => $id))->save($data)) {
                $this->error("修改失败！");
            } else {
                addOrderLog($id,'修改消耗品实时库存',$str);
                $this->success('修改成功！', U('inventorylist'));
            }
        } else {
            $data = M('subpro_inventory')->where(array('id' => $id))->find();
            $this->data = $data;
            $this->display();
        }
    }
    public function inventorydel($remark="") {
        $id = I('id');
        if (IS_POST) {
            $spu = I('spu');
            $pname = I('pname');
            $spec = I('spec');
            $cnt = I('cnt');
            $type = "消耗品";

            $str = "商品分类：消耗品;商品编号：".$spu.";商品名称：".$pname.";规格：".$spec.";库存数量：".$cnt.";商品类型：".$type.";删除原因：".$remark;

            if ($remark == "") {
                $this->error("删除原因不能为空");
            }

            $res = M('Subpro_inventory')->where(array('id' => $id))->delete();
            if($res !== false){
                addOrderLog($id,'删除消耗品实时库存',$str);
                $this->success('删除成功！', U('inventorylist'));
            }else {
                $this->error('删除失败！');
            }
        } else {
            $data = M('subpro_inventory')->where(array('id' => $id))->find();
            $this->assign('_type', $data['type']);
            $this->data = $data;
            $this->display();
        }
    }
    public function ajaxGetSubPro(){
        $model = M('subpro_product');
        $map['status']=1;
        $res=$model->where($map)->select();
        $this->ajaxReturn($res);
    }
    public function department(){
        $map['status'] = 1;
        $res=M('subpro_department')->where($map)->select();
        $this->assign('_list',$res);
        $this->meta_title = '使用部门列表';
        $this->display();
    }

    public function departmentadd(){
        if(IS_POST){
            $data['name']=I('name');
            $model=M('subpro_department');
            $id=$model->add($data);
            if (!$id) {
                $this->error("添加失败！");
            } else {
                $this->success('添加成功！', U('department'));
            }
        }else{
            $this->meta_title = "使用部门添加";
            $this->display();
        }
    }

    public function departmentedit(){
        $map['id'] = I('id');
        if(IS_POST){
            $data['name']=I('name');
            $model=M('subpro_department');
            $id=$model->where($map)->save($data);
            if (!$id) {
                $this->error("编辑失败！");
            } else {
                $this->success('编辑成功！', U('department'));
            }
        }else{
            $res = M('subpro_department')->where($map)->find();
            $this->classify = $res;
            $this->meta_title = "使用部门编辑";
            $this->display();
        }
    }

    public function departmentdel(){
        $map['id'] = I('id');
        $model=M('subpro_department');
        $data['status']=-1;
        $id= $model->where($map)->save($data);
        if (!$id) {
            $this->error("删除失败！");
        } else {
            $this->success('删除成功！', U('department'));
        }
    }

    public function instorageindex($id="",$pname="",$unit="",$number="",$spec="",$totalprice=""){
        /*$shoplist = M('subpro_department')->where(array('status' => 1))->select();
        $this->assign('shoplist', $shoplist);*/
        if(IS_POST){
            if($unit==""||$id==""||$pname==""||$number==""||$unit==""||$spec==""||$totalprice==""){
                $this->error("入库信息不完整");
            }
            $stock = M('subpro_productstock');
            $res = $stock ->where(array('BIN'=>$id,'dpid'=>999))->find();
            if($res){
                $oldtotalprice = $res['totalprice'];
                $oldcnt = $res['cnt'];
                $cnt = $oldcnt + $number;
                $newtotalprice = $oldtotalprice + $totalprice;
                $unitprice = $newtotalprice/$cnt;
                $stock->where(array('BIN'=>$id,'dpid'=>999))->save(array('cnt'=>$cnt,'unitprice'=>$unitprice,'totalprice'=>$newtotalprice));
            }else{
                $data['BIN'] = $id;
                $data['pname'] = $pname;
                $data['dpid'] = 999;
                $data['spec'] = $spec;
                $data['cnt'] = $number;
                $data['mother_unit'] = I('mother_unit');
                $data['son_unit'] = I('son_unit');
                $data['mother_to_son'] = I('mother_to_son');
                $data['unit'] = $unit;
                $data['unitprice'] = $totalprice/$number;
                $data['totalprice'] = $totalprice;
                $data['createAt'] = NOW_TIME;
                $data['updateAt'] = NOW_TIME;
                $stock->add($data);
            }
            $datalist = array(
                'dpid' => 999,
                'BIN' => $id,
                'spec' => $spec,
                'pname' => $pname,
                'cnt' => $number,
                'unit' => $unit,
                'createAt' => NOW_TIME,
                'updateAt' => NOW_TIME,
                'unitprice' => $totalprice/$number,
                'totalprice' => $totalprice,
                'aftercnt' => $number,
                'type'=>'in'
            );
            M('subpro_act')->add($datalist);
            $this->success('入库成功',U('subpro/instorageindex'));
        }else{
            $this->meta_title = '消耗品入库';
            $this->display();
        }
    }

    public function subprolistdepartment(){
        $shoplist = M('subpro_department')->where(array('status' => 1))->select();
        $this->assign('shoplist', $shoplist);
        $this->meta_title = '消耗品库存';
        $this->display();
    }

    public function subprolist($pname=""){
        $shopId = I('shopId');
        if($pname){
            $where=" and  b.pname like '%".$pname."%'";
        }
        $this->assign('shopId',$shopId);
        $model=new Model();
        $sql="select a.*,b.cnt_poor,b.cnt_low,CASE WHEN a.cnt<=b.cnt_low THEN 1 ".
            "WHEN b.cnt_low<a.cnt and a.cnt<=b.cnt_poor THEN 2 ".
            "WHEN a.cnt>b.cnt_poor THEN 3 END as cntstatus".
            " from gr_subpro_productstock a,gr_subpro_product b where cnt>0 and a.BIN=b.id and dpid=".I('shopId').$where;
        $res = $model->query($sql);
        $total = count($res);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }
        $page = new \Think\Page($total, $listRows, $REQUEST);
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        $sql.=" limit $page->firstRow ,$page->listRows";
        $res = $model->query($sql);
        $this->assign("_list",$res);
        $this->meta_title = '数据列表';
        $this->display();
    }

    public function outstorageindex(){
        $shoplist = M('subpro_department')->where(array('status' => 1))->select();
        $this->assign('shoplist', $shoplist);
        $this->meta_title = '消耗品申请出库部门选择';
        $this->display();
    }
    public function outstoragedepartment(){
        $shopId = I('shopId');
        $this->assign('shopId',$shopId);
        $model=new Model();
        $map['dpid'] = $shopId;
        $sql="select batch,createAt,status from gr_subpro_apply WHERE dpid=$shopId group by batch ";
        $count=$model->query($sql);
        $this->assign('_list',$count);
        $this->display();
    }
    public function outstorageapplydetail(){
        $map['batch'] = I('batch');
        $res= M('subpro_apply')->where($map)->select();
        $this->assign('_list',$res);
        $this->assign('data',$res[0]);
        $this->display();
    }
    public function outstorageapplyadd(){


        $shopId = I('shopId');
        $this->assign('shopId',$shopId);
        $this->display();
    }

    public function applyreview(){
        $model=new Model();
        $sql="select batch,dpid,createAt,status from gr_subpro_apply WHERE status=1 group by batch ";
        $count=$model->query($sql);
        $this->assign('_list',$count);
        $this->display();
    }

    public function applyreviewdetail(){
        $map['batch'] = I('batch');
        $res= M('subpro_apply')->where($map)->select();
        $this->assign('_list',$res);
        $this->assign('data',$res[0]);
        $this->display();
    }

    public function applychangestatus(){
        $map['batch']=I('batch');
        $data['status']= I('status');
        if (!M('subpro_apply')->where($map)->save($data)) {
            $this->error('操作失败！',U('subpro/applyreview'));
        } else {
            $this->success('操作成功！',U('subpro/applyreview'));
        };
    }

    public function applydistribution(){
        $model=new Model();
        $sql="select batch,dpid,createAt,status from gr_subpro_apply WHERE status=2 group by batch ";
        $count=$model->query($sql);
        $this->assign('_list',$count);
        $this->display();
    }

    public function applydistributiondetail($batch=""){
        $sql="select a.*,b.cnt as inventory,c.pname from gr_subpro_apply a,gr_subpro_productstock b,gr_subpro_product c where a.BIN=b.BIN and a.batch=$batch and b.dpid=999 and a.BIN=c.id";
        $model=new Model();
        $res=$model->query($sql);
        $count=count($res);
        $this->assign('count',$count);
        $this->assign('data',$res[0]);
        $this->assign('_list',$res);
        $this->display();
    }

    public function applydistributiongo(){
        $res['realitycnt']=I('realitycnt');
        $res['unitprice']=I('unitprice');
        $res['BIN']=I('BIN');
        $batchIdArr=I('batchid');
        $count=I('count');
        $model=M('subpro_apply');
        $model->startTrans();
        $flag=true;
        for($i=0;$i<$count;$i++){
            //配货
            $data['realitycnt']=$res['realitycnt'][$i];
            $mapb['BIN'] = $res['BIN'][$i];
            $mapb['dpid'] = 999;
            $oldstock=M('subpro_productstock')->where($mapb)->find();
            if($data['realitycnt']>$oldstock['cnt']){

                $flag=false;
            }else{
                $data['unitprice']=$res['unitprice'][$i];
                $data['totalprice']= $data['realitycnt']*$data['unitprice'];
                $data['status']=4;
                $mapa['batch'] = I('batch');
                $mapa['BIN'] = $res['BIN'][$i];
                $applyRes=M('subpro_apply')->where(array('id'=>$batchIdArr[$i]))->save($data);
                if($applyRes===false){
                    $flag=false;
                }
                //更新库存表
                $newstock['cnt']=$oldstock['cnt']-$data['realitycnt'];
                $newstock['totalprice']=$oldstock['totalprice']-$data['totalprice'];
                M('subpro_productstock')->where($mapb)->save($newstock);
                //插入流水表
                $mapc['id'] = $res['BIN'][$i];
                $product=M('subpro_product')->where($mapc)->find();
                $news=M('subpro_apply')->where($mapa)->find();
                $act['dpid'] = 999;
                $act['BIN'] = $mapc['id'];
                $act['pname'] = $product['pname'];
                $act['spec'] = $news['spec'];
                $act['unit'] = $news['unit'];
                $act['cnt'] = $news['realitycnt'];
                $act['type'] = 'out';
                $act['createAt'] = NOW_TIME;
                $act['updateAt'] = NOW_TIME;
                $act['unitprice'] = $news['unitprice'];
                $act['totalprice'] = $data['totalprice'];
                $act['beforecnt'] = $oldstock['cnt'];
                $act['aftercnt'] = $newstock['cnt'];
                if(M('subpro_act')->add($act)){
                }else{

                    $flag=false;
                }
            }
        }

        if($flag){
            $model->commit();
            $this->success('操作成功！',U('subpro/applydistribution'));
        }else{
            $model->rollback();
            $this->error('操作失败！',U('subpro/applydistribution'));
        }
    }

    public function applysign(){
        $map['id'] = array('neq',999);
        $map['status'] = 1;
        $shoplist = M('subpro_department')->where($map)->select();
        $this->assign('shoplist', $shoplist);
        $this->meta_title = '消耗品配货签收部门选择';
        $this->display();
    }

    public function applysigndepartment(){
        $shopId = I('shopId');
        $this->assign('shopId',$shopId);
        $model=new Model();
        $map['dpid'] = $shopId;
        $sql="select batch,createAt,status from gr_subpro_apply WHERE dpid=$shopId AND (status=5 OR status=4) group by batch ORDER BY createAt desc";
        $count=$model->query($sql);
        $this->assign('_list',$count);
        $this->display();
    }

    public function applysigndetail(){
        $map['batch'] = I('batch');
        $res= M('subpro_apply')->where($map)->select();
        $this->assign('_list',$res);
        $count=count($res);
        $this->assign('count',$count);
        $this->assign('data',$res[0]);
        $this->display();
    }

    public function applysigngo(){
        $map['batch'] = I('batch');
        $data['status']= 5;
        $data['updateAt']=NOW_TIME;
        $res=M('subpro_apply')->where($map)->select();
        for($i=0;$i<count($res);$i++){
            $mapa['dpid']=$res[$i]['dpid'];
            $mapa['BIN']=$res[$i]['BIN'];
            //更新库存表
            $oldstock=M('subpro_productstock')->where($mapa)->find();
            if($oldstock){
                //更新库存表
                $newstock['cnt']=$oldstock['cnt']+$res[$i]['realitycnt'];
                $newstock['totalprice']=$oldstock['totalprice']+$res[$i]['totalprice'];
                $newstock['unitprice']=$newstock['totalprice']/$newstock['cnt'];
                M('subpro_productstock')->where($mapa)->save($newstock);
                //插流水
                $mapc['id']=$res[$i]['BIN'];
                $product=M('subpro_product')->where($mapc)->find();
                $newact['dpid']=$res[$i]['dpid'];
                $newact['BIN']=$res[$i]['BIN'];
                $newact['pname']=$product['pname'];
                $newact['spec']=$res[$i]['spec'];
                $newact['unit']=$res[$i]['unit'];
                $newact['cnt']=$res[$i]['realitycnt'];
                $newact['type']='in';
                $newact['createAt']=NOW_TIME;
                $newact['updateAt']=NOW_TIME;
                $newact['unitprice']=$res[$i]['unitprice'];
                $newact['totalprice']=$res[$i]['totalprice'];
                $newact['beforecnt']=$oldstock['cnt'];
                $newact['aftercnt']=$newstock['cnt'];
                M('subpro_act')->add($newact);
            }else{
                //插入库存表
                $mapb['id']=$res[$i]['BIN'];
                $product=M('subpro_product')->where($mapb)->find();
                $newstock['BIN']=$res[$i]['BIN'];
                $newstock['dpid']=$res[$i]['dpid'];
                $newstock['createAt']=NOW_TIME;
                $newstock['updateAt']=NOW_TIME;
                $newstock['mother_unit']=$product['mother_unit'];
                $newstock['son_unit']=$product['son_unit'];
                $newstock['mother_to_son']=$product['mother_to_son'];
                $newstock['cnt_low']=$product['cnt_low'];
                $newstock['cnt_poor']=$product['cnt_poor'];
                $newstock['unitprice']=$res[$i]['unitprice'];
                $newstock['totalprice']=$res[$i]['totalprice'];
                $newstock['cnt']=$res[$i]['realitycnt'];
                /*dump($newstock);*/
                M('subpro_productstock')->add($newstock);
                //插流水
                $newact['dpid']=$res[$i]['dpid'];
                $newact['BIN']=$res[$i]['BIN'];
                $newact['pname']=$product['pname'];
                $newact['spec']=$res[$i]['spec'];
                $newact['unit']=$res[$i]['unit'];
                $newact['cnt']=$res[$i]['realitycnt'];
                $newact['type']='in';
                $newact['createAt']=NOW_TIME;
                $newact['updateAt']=NOW_TIME;
                $newact['unitprice']=$res[$i]['unitprice'];
                $newact['totalprice']=$res[$i]['totalprice'];
                $newact['beforecnt']=0;
                $newact['aftercnt']=$res[$i]['realitycnt'];
                /*dump($newact);*/
                M('subpro_act')->add($newact);
            }

        }

        if (!M('subpro_apply')->where($map)->save($data)) {
            $this->error('操作失败！',U('subpro/applysign'));
        } else {
            $this->success('操作成功！',U('subpro/applysign'));
        };
    }

    public function yearexpend(){
        $model=new Model();
        $sql="select sum(unitprice*cnt) as total_fee,DATE_FORMAT(FROM_UNIXTIME(createAt),'%Y') as year from gr_subpro_act where type='out' and dpid=999 GROUP BY DATE_FORMAT(FROM_UNIXTIME(createAt),'%Y')";
        $res=$model->query($sql);
        $this->_list=$res;
        $this->display();
    }
    public function yeardepartmentexpend($year="",$export=0){
        $firstday = date('Y-01-01 00:00:00', strtotime($year));
        $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 year -1 day"));
        $startTime=strtotime($firstday);
        $endTime=strtotime($lastday);
        $where=" and createAt >= $startTime and createAt <= $endTime and type='in' and dpid<999 ";
        $sql="select sum(unitprice*cnt) as total_fee,dpid from gr_subpro_act where 1=1 $where group by dpid";
        $model=new Model();
        $res=$model->query($sql);
        $data=[];
        foreach($res as $k=>$v){
            $data[$k]['dpid']=$v['dpid'];
            $data[$k]['department']=get_subpro_department_name($v['dpid']);
            $data[$k]['total_fee']=$v['total_fee'];
        }
        $data=multi_array_sort($data,'total_fee');
        if($export==0){
            $this->_list=$data;
            $this->title=substr($year,0,4)."年度各部门总消耗";
            $this->year=$year;
            $this->display();
        }else{
            if ($data) {
                //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                import("Org.Util.PHPExcel");
                import("Org.Util.PHPExcel.Writer.Excel5");
                import("Org.Util.PHPExcel.IOFactory.php");
                $filename = "output_order_excel";

                $headArr = array("部门编号","部门", "总消耗");
                $title = substr($year,0,4)."年度各部门总消耗";
                $this->getExcel($filename, $headArr, $data, $title);
            }else{
                $this->error("没有可导出的数据！");
            }
        }
    }
    public function yeardepartmentproduct($year="",$dpid="",$export=0){
        $firstday = date('Y-01-01 00:00:00', strtotime($year));
        $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 year -1 day"));
        $startTime=strtotime($firstday);
        $endTime=strtotime($lastday);
        $where=" and createAt >= $startTime and createAt <= $endTime and dpid=$dpid and type='in' ";
        $sql="select pname,unit, sum(cnt*unitprice) as total_fee,sum(cnt) as cnt from gr_subpro_act where 1=1 $where group by pname";
        $model=new Model();
        $res=$model->query($sql);
        $data=multi_array_sort($res,'total_fee');
        if($export==0){
            $this->title=substr($year,0,4)."年度".get_subpro_department_name($dpid)."消耗明细";
            $this->_list=$data;
            $this->display();
        }else{
            if ($data) {
                //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                import("Org.Util.PHPExcel");
                import("Org.Util.PHPExcel.Writer.Excel5");
                import("Org.Util.PHPExcel.IOFactory.php");
                $filename = "output_order_excel";

                $headArr = array("名称", "规格", "消耗总价", "消耗数量");
                $title = substr($year,0,4)."年度".get_subpro_department_name($dpid)."消耗明细";
                $this->getExcel($filename, $headArr, $data, $title);
            }else{
                $this->error("没有可导出的数据！");
            }
        }

    }

    public function monthexpend(){
        $model=new Model();
        $sql="select sum(unitprice*cnt) as total_fee,DATE_FORMAT(FROM_UNIXTIME(createAt),'%Y%m') as month from gr_subpro_act where type='out' and dpid=999 GROUP BY DATE_FORMAT(FROM_UNIXTIME(createAt),'%Y%m')";
        $res=$model->query($sql);
        $this->_list=$res;
        $this->display();
    }
    public function monthdepartmentexpend($month=""){
        $firstday = date('Y-m-01 00:00:00', strtotime($month));
        $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 month -1 day"));
        $startTime=strtotime($firstday);
        $endTime=strtotime($lastday);
        $where=" and createAt >= $startTime and createAt <= $endTime and type='in' and dpid<999 ";
        $sql="select sum(unitprice*cnt) as total_fee,dpid from gr_subpro_act where 1=1 $where group by dpid";
        $model=new Model();
        $res=$model->query($sql);
        $data=[];
        foreach($res as $k=>$v){
            $data[$k]['total_fee']=$v['total_fee'];
            $data[$k]['department']=get_subpro_department_name($v['dpid']);
            $data[$k]['dpid']=$v['dpid'];
        }
        $data=multi_array_sort($data,'total_fee');
        $this->_list=$data;
        $this->month=$month;
        $this->display();
    }
    public function monthdepartmentproduct($month="",$dpid="",$department=''){
        $firstday = date('Y-m-01 00:00:00', strtotime($month));
        $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 month -1 day"));
        $startTime=strtotime($firstday);
        $endTime=strtotime($lastday);
        $where=" and createAt >= $startTime and createAt <= $endTime and dpid=$dpid and type='in' ";
        $sql="select pname,unit, sum(cnt*unitprice) as total_fee,sum(cnt) as cnt from gr_subpro_act where 1=1 $where group by pname";
        $model=new Model();
        $res=$model->query($sql);
        $this->_list=$res;
        $this->assign('month',$month);
        $this->assign('dpid',$dpid);
        $this->assign('department',$department);
        $this->display();
    }

    public function exportmonthdepartmentproduct($month="",$dpid="",$department=''){
        if (!IS_POST) {
            $this->redirect("subpro/monthdepartmentproduct");
        } else {
            if ($month == null||$dpid == null) {
                $this->error("参数有误！");
            } else {
                $firstday = date('Y-m-01 00:00:00', strtotime($month));
                $lastday = date('Y-m-d 23:59:59', strtotime("$firstday +1 month -1 day"));
                $startTime=strtotime($firstday);
                $endTime=strtotime($lastday);
                $where=" and createAt >= $startTime and createAt <= $endTime and dpid=$dpid and type='in' ";
                $sql="select pname,unit, sum(cnt*unitprice) as total_fee,sum(cnt) as cnt from gr_subpro_act where 1=1 $where group by pname";
                $model=new Model();
                $dataTable=$model->query($sql);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");
                    $month=substr($month,0,6);
                    $filename = "$department $month 消耗品领用明细";

                    $headArr = array("品名", "单位", "金额", "数量");

                    $title = $filename;
                    $this->getExcel($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }

    public function periodexpend($startDate="",$endDate=""){
        if($startDate==""){
            $startDate=date('Y-m-d 00:00:00',time());
        }else{
            $startDate=date('Y-m-d 00:00:00',strtotime($startDate));
        }
        if($endDate==""){
            $endDate=date('Y-m-d 23:59:59',time());
        }else{
            $endDate=date('Y-m-d 23:59:59',strtotime($endDate));
        }
        $startTime=strtotime($startDate);
        $endTime=strtotime($endDate);
        $model=new Model();
        $sql="select sum(unitprice*cnt) as total_fee from gr_subpro_act where type='out' and dpid=999 and createAt>$startTime and createAt<$endTime";
        $res=$model->query($sql);
        $this->_list=$res;
        $this->startDate=$startDate;
        $this->endDate=$endDate;
        $this->display();
    }
    public function perioddepartmentexpend($startDate="",$endDate=""){
        $startTime=strtotime($startDate);
        $endTime=strtotime($endDate);
        $sql="select sum(unitprice*cnt) as total_fee,dpid from gr_subpro_act where type='in' and dpid<>999 and createAt>$startTime and createAt<$endTime group by dpid";
        $model=new Model();
        $res=$model->query($sql);
        $total = count($res);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }
        $page = new \Think\Page($total, $listRows, $REQUEST);
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        $sql.=" limit $page->firstRow ,$page->listRows";
        $res=$model->query($sql);
        $this->_list=$res;
        $this->startDate=$startDate;
        $this->endDate=$endDate;
        $this->display();
    }
    public function perioddepartmentproduct($startDate="",$endDate="",$dpid=""){
        $startTime=strtotime($startDate);
        $endTime=strtotime($endDate);
        $where=" and createAt >= $startTime and createAt <= $endTime and dpid=$dpid and type='in' ";
        $sql="select pname,unit, sum(cnt*unitprice) as total_fee,sum(cnt) as cnt from gr_subpro_act where 1=1 $where group by pname";
        $model=new Model();
        $res=$model->query($sql);
        $this->_list=$res;
        $this->display();
    }

    public function subproapplyindex(){
        $model=M('Subpro_department');
        $map['status']=1;
        $res=$model->where($map)->order('id desc')->select();
        $this->_list=$res;
        $this->display();
    }
    public function subproapplylist($dpid=""){
        $map['dpid']=$dpid;
        $model=M('Subpro_apply');
        $res=$model->where($map)->group('batch')->order('createAt desc')->select();
        $total = count($res);
        if (isset($REQUEST['r'])) {
            $listRows = (int) $REQUEST['r'];
        } else {
            $listRows = C('LIST_ROWS') > 0 ? C('LIST_ROWS') : 10;
        }
        $page = new \Think\Page($total, $listRows, $REQUEST);
        if ($total > $listRows) {
            $page->setConfig('theme', '%FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END% %HEADER%');
        }
        $p = $page->show();
        $this->assign('_page', $p ? $p : '');
        $this->assign('_total', $total);
        $res=$model->where($map)->group('batch')->order('createAt desc')->limit($page->firstRow,$page->listRows)->select();
        $this->_list=$res;
        $this->dpid=$dpid;
        $this->display();
    }
    public function subproapplylistdetail($batch=""){
        $map['batch']=$batch;
        $model=M('Subpro_apply');
        $res=$model->where($map)->select();
        $this->_list=$res;
        $this->display();
    }
    public function subproapplyadd($dpid=""){
        if(IS_POST){
            $dpidArr=I('dpid');
            $unitArr=I('unit');
            $pidArr=I('pid');
            $specArr=I('spec');
            $cntArr=I('number');
            $unitpriceArr=I('unitprice');
            $totalpriceArr=I('totalprice');
            $dataList=[];
            $timeStamp=time();
            $batch=$dpidArr[0].$timeStamp;
            $model=M('Subpro_apply');
            for($i=0;$i<count($pidArr);$i++){
                if($pidArr[$i]==""||$pidArr[$i]==0){
                    continue;
                }
                $dataList[]=array(
                    'batch'=>$batch,
                    'dpid'=>$dpidArr[$i],
                    'unit'=>$unitArr[$i],
                    'BIN'=>$pidArr[$i],
                    'spec'=>$specArr[$i],
                    'cnt'=>$cntArr[$i],
                    'unitprice'=>$unitpriceArr[$i],
                    'realitycnt'=>$cntArr[$i],
                    'createAt'=>time(),
                    'status'=>1,
                    'totalprice'=>$totalpriceArr[$i],
                );
            }
            $res=$model->addAll($dataList);
            if($res){
                $this->success("申请成功，请等待审核",U('Subpro/subproapplyindex'));
            }else{
                $this->error("申请失败，请重试");
            }
        }else{
            $this->dpid=$dpid;
            $this->title="消耗品新增";
            $this->display();
        }
    }

    public function checkindex(){
        $model=M('Subpro_department');
        $map['status']=1;
        $res=$model->where($map)->order('id desc')->select();
        $this->_list=$res;
        $this->display();
    }
    public function checkproductlist($dpid=999){
        if(IS_POST){
            $BINArr=I('BIN');
            $realcntArr=I('realcnt');
            $dpidArr=I('dpid');
            $model=M('Subpro_productstock');
            $model->startTrans();
            $flag=true;
            $dataList=[];
            for($i=0;$i<count($BINArr);$i++){
                $map['BIN']=$BINArr[$i];
                $map['dpid']=$dpidArr[$i];
                $info=$model->where($map)->find();
                $data['cnt']=$realcntArr[$i];
                $data['totalprice']=$info['unitprice']*$realcntArr[$i];
                $res=$model->where($map)->save($data);
                if($res===false){
                    $flag=false;
                }
                $dataList[]=array(
                    'BIN'=>$BINArr[$i],
                    'beforecnt'=>$info['cnt'],
                    'aftercnt'=>$realcntArr[$i],
                    'createAt'=>time(),
                    'unitprice'=>$info['unitprice'],
                    'dpid'=>$dpidArr[$i],
                    'unit'=>$info['mother_to_son'].$info['son_unit'].'/'.$info['mother_unit'],
                    'uid'=>UID,
                    'type'=>'check'
                );
            }
            if($flag){
                $model->commit();
                M('Subpro_act')->addAll($dataList);
                $this->success('保存成功');
            }else{
                $this->error('保存失败');
                $model->rollback();
            }
        }else{
            $map['dpid']=$dpid;
            $res=$this->lists('Subpro_productstock',$map);
            $this->_list=$res;
            $this->display();
        }
    }

    public function subproprint($batch=""){
        $map['batch']=$batch;
        $res=M('Subpro_apply')->where($map)->select();
        $this->_list=$res;
        $this->display();
    }
    public function ajaxGetSubproProductList(){
        $sql="select a.*,b.unitprice,b.BIN,b.cnt,b.totalprice from gr_subpro_product a,gr_subpro_productstock b where a.status=1 AND b.dpid=999 and a.id=b.BIN";
        $model = new Model();
        $res=$model->query($sql);
        $this->ajaxReturn($res);
    }
}
